﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;

namespace QAToolsAddin {
    static class Utils {

        public delegate Out Converter<In, Out>(In obj);

        public static Out[] ListToArray<In, Out>(IList<In> list, Converter<In, Out> itemConverter) {
            var len = list.Count;
            var values = new Out[len];
            for (int i = 0; i < len; i++)
                values[i] = itemConverter(list[i]);
            return values;
        }


        internal static DateTime MinDate(DateTime a, DateTime b) {
            if (b < a)
                return b;
            return a;
        }


        internal static CultureInfo GetSystemCulture() {
            CultureInfo culture = null;
            var thread = new System.Threading.Thread(() => culture = System.Threading.Thread.CurrentThread.CurrentCulture);
            thread.Start();
            thread.Join();
            return culture;
        }

        internal static void SwitchExcelToIdle(Excel.Application excelapp, bool on) {
            excelapp.ScreenUpdating = @on;
            excelapp.DisplayAlerts = @on;
            excelapp.Calculation = @on ? Excel.XlCalculation.xlCalculationAutomatic : Excel.XlCalculation.xlCalculationManual;
            excelapp.UserControl = @on;
            excelapp.EnableEvents = @on;
            excelapp.Cursor = @on == false ? Excel.XlMousePointer.xlWait : Excel.XlMousePointer.xlDefault;
        }

        internal static object[,] GetTitlesFromRange(Excel.Range range, bool hasheaders) {
            if (range == null)
                throw new ArgumentNullException("range");
            object[,] titles;
            var rgTiles = (Excel.Range)range.Rows[1];
            int nbCol = rgTiles.Columns.Count;
            titles = ExcelArray<object>.Create(1, nbCol);
            if (hasheaders == false) {
                int firstCol = range.Column;
                for (int i = 0; i < nbCol; i++)
                    titles[1, i + 1] = "Col" + Utils.GetColumnLetters(firstCol + i);
            } else {
                var lTitles = ExcelArray<object>.Get(rgTiles.Value);
                for (int ci = 1; ci <= nbCol; ci++)
                    titles[1, ci] = lTitles[1, ci].ToString();
            }
            return titles;
        }

        internal static void AddTitlesToRange(Excel.Range targetrange, object[,] data) {
            if (data == null) throw new ArgumentNullException("data");
            if (targetrange == null) throw new ArgumentNullException("targetrange");
            targetrange = targetrange.Resize[data.GetLength(0), data.GetLength(1)];
            targetrange.Value = data;
            targetrange.Font.Bold = true;
        }

        internal static void AddDataToRange(Excel.Range targetrange, object[,] data, bool useFormula = false, object[,] titles = null) {
            if (data == null) throw new ArgumentNullException("data");
            if (targetrange == null) throw new ArgumentNullException("targetrange");
            targetrange = targetrange.Resize[data.GetLength(0), data.GetLength(1)];
            try {
                if (useFormula)
                    targetrange.Formula = data;
                else
                    targetrange.Value = data;
                if (titles != null) {
                    if (targetrange.Row == 1) throw new Exception("No place to add the titles");
                    Excel.Range rgTitles = (Excel.Range)targetrange.Rows[0];
                    rgTitles.NumberFormat = "@";
                    rgTitles.Value = titles;
                    rgTitles.Font.Bold = true;
                }
            } catch (Exception) {
                //Retry with strings trunceted to a length of 900.
                TruncateStringsOver900Charaters(ref data);
                if (useFormula)
                    targetrange.Formula = data;
                else
                    targetrange.Value = data;
            }
        }

        internal static object[,] GetDataFromRange(Excel.Range range, bool useFormula) {
            if (range == null)
                throw new ArgumentNullException("range");
            return ExcelArray<object>.Get(useFormula ? range.Formula : range.Value);
        }

        internal static Excel.Style GetStyle(Excel.Workbook wb, string name) {
            foreach (Excel.Style style in wb.Styles)
                if (style.Name == name) return style;
            return null;
        }

        internal static void SetTextArray(Excel.Range range, string[,] array) {
            range.NumberFormat = "@";
            range.Formula = array;
        }

        internal static void AddTagsToRange(Excel.Range targetrange, bool[,] diff, int interiorColor = 0, int fontColor = 0) {
            if (targetrange == null)
                throw new ArgumentNullException("targetrange");
            var workbook = (Excel.Workbook)targetrange.Worksheet.Parent;
            var excelapp = targetrange.Application;

            Excel.Style xlStyle0 = GetStyle(workbook, "TagNormal");
            if (xlStyle0 == null) {
                xlStyle0 = workbook.Styles.Add("TagNormal", Type.Missing);
                xlStyle0.IncludeNumber = false;
                xlStyle0.IncludeAlignment = false;
                xlStyle0.IncludeBorder = false;
                xlStyle0.IncludeProtection = false;
            }

            if (diff == null) {
                targetrange.Style = xlStyle0;
                return;
            }

            Excel.Style xlStyle1 = GetStyle(workbook, "TagDiff");
            if (xlStyle1 == null) {
                xlStyle1 = workbook.Styles.Add("TagDiff", Type.Missing);
                xlStyle1.IncludeNumber = false;
                xlStyle1.IncludeAlignment = false;
                xlStyle1.IncludeBorder = false;
                xlStyle1.IncludeProtection = false;
            }
            xlStyle1.Interior.Color = interiorColor;
            xlStyle1.Font.Color = fontColor;

            var nbRow = diff.GetLength(0);
            var nbCol = diff.GetLength(1);
            var range = targetrange.Resize[nbRow, nbCol];
            range.Style = xlStyle0;

            List<object> ranges = new List<object>(30);
            int[] maxRow = new int[nbCol + 1];
            for (var ri = 1; ri <= nbRow; ri++) {
                for (var ci = 1; ci <= nbCol; ci++) {
                    if (ri <= maxRow[ci]) continue;
                    if (diff[ri, ci]) {
                        int nbDiffCol = 0, nbDiffRow = 0;
                        for (var ri2 = ri + 1; ri2 <= nbRow && diff[ri2, ci]; ri2++)
                            nbDiffRow++;
                        for (var ci2 = ci + 1; ci2 <= nbCol && diff[ri, ci2]; ci2++)
                            nbDiffCol++;
                        var diffRange = (Excel.Range)targetrange.Cells[ri, ci];
                        if (nbDiffCol == 0 && nbDiffRow == 0)
                            ranges.Add(diffRange);
                        else {
                            if (nbDiffCol != 0)
                                ranges.Add(diffRange.Resize[Type.Missing, nbDiffCol + 1]);
                            if (nbDiffRow != 0)
                                ranges.Add(diffRange.Resize[nbDiffRow + 1, Type.Missing]);
                        }
                        maxRow[ci] = ri + nbDiffRow;
                        ci += nbDiffCol;
                    }
                }
            }
            int rgLen = ranges.Count;
            if (rgLen != 0) {
                int mod = rgLen % 30;
                if (mod > 1) {
                    for (var i = mod; i < 30; i++)
                        ranges.Add(Type.Missing);
                }
                for (int i = 0; i < rgLen; i += 30) {
                    if (i == rgLen - 1)
                        ((Excel.Range)ranges[i]).Style = xlStyle1;
                    else
                        excelapp.Union((Excel.Range)ranges[i], (Excel.Range)ranges[i + 1], ranges[i + 2], ranges[i + 3], ranges[i + 4], ranges[i + 5], ranges[i + 6], ranges[i + 7], ranges[i + 8], ranges[i + 9], ranges[i + 10], ranges[i + 11], ranges[i + 12], ranges[i + 13], ranges[i + 14], ranges[i + 15], ranges[i + 16], ranges[i + 17], ranges[i + 18], ranges[i + 19], ranges[i + 20], ranges[i + 21], ranges[i + 22], ranges[i + 23], ranges[i + 24], ranges[i + 25], ranges[i + 26], ranges[i + 27], ranges[i + 28], ranges[i + 29]).Style = xlStyle1;
                }
            }
        }

        internal static int[] GetVisibleColumnsPosition(Excel.Range baseRange, Excel.Range innerRange = null, int[] excludedPositions = null) {
            if (baseRange == null)
                throw new ArgumentNullException("baseRange");
            var exclude = excludedPositions == null ? new List<int>() : new List<int>(excludedPositions);
            if (innerRange == null)
                innerRange = baseRange;
            int baseCol = baseRange.Column - 1;
            var keys = new List<int>();
            foreach (Excel.Range col in innerRange.Columns) {
                if ((int)(double)col.Width != 0) {
                    int position = col.Column - baseCol;
                    if (!exclude.Contains(position))
                        keys.Add(position);
                }
            }
            return keys.ToArray();
        }

        internal static void TruncateStringsOver900Charaters(ref object[,] data) {
            if (data == null)
                throw new ArgumentNullException("data");
            int nbRow = data.GetLength(0);
            int nbCol = data.GetLength(1);
            for (var ri = 1; ri <= nbRow; ri++) {
                for (var ci = 1; ci <= nbCol; ci++) {
                    if (data[ri, ci] is string && ((string)data[ri, ci]).Length > 900)
                        data[ri, ci] = (data[ri, ci] as string).Substring(0, 900) + "...";
                }
            }
        }

        internal static Excel.Range ExtendRange(Excel.Range range) {
            if (range == null)
                throw new ArgumentNullException("range");
            var excelapp = range.Application;
            if (((Excel.Range)range[1, 1]).Value != null && range.Rows.Count == 1) {
                int lmaxCol;
                if (((Excel.Range)range[1, 2]).Value == null || range.Columns.Count > 1)
                    lmaxCol = range.Column + range.Columns.Count - 1;
                else
                    lmaxCol = range.End[Excel.XlDirection.xlToRight].Column;
                var lastCell = range.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
                var lmaxRow = ((Excel.Range)excelapp.Cells[lastCell.Row + 1, range.Column]).End[Excel.XlDirection.xlUp].Row;
                return excelapp.Range[range, excelapp.Cells[lmaxRow, lmaxCol]];
            }
            return range;
        }

        internal static bool RangeHasHeaders(Excel.Range range) {
            if (range == null) return false;
            if (range.ListObject != null && range.ListObject.Range.Row == range.Row)
                return true;
            object bold = ((Excel.Range)range.Rows[1]).Font.Bold;
            if (bold is System.DBNull) return false;
            return (bool)bold == true;
        }

        internal static void SetCellStyle(Excel.Range range, int fontColor = -1, int interiorColor = -1) {
            if (range == null)
                throw new ArgumentNullException("range");
            if (fontColor != -1)
                range.Font.Color = fontColor;
            if (interiorColor != -1)
                range.Interior.Color = interiorColor;
        }

        internal static void SetBorderStyle(Excel.Range range, Excel.XlLineStyle linestyle, Excel.XlBorderWeight weight, object color, params Excel.XlBordersIndex[] borderindex) {
            if (range == null)
                throw new ArgumentNullException("range");
            if (borderindex != null) {
                foreach (Excel.XlBordersIndex border in borderindex) {
                    Excel.Border rg = range.Borders[border];
                    rg.LineStyle = linestyle;
                    rg.Weight = weight;
                    rg.Color = color;
                }
            } else {
                var rg = range.Borders;
                rg.LineStyle = linestyle;
                rg.Weight = weight;
                rg.Color = color;
            }
        }

        internal static string GetRangeAddress(Excel.Range range, bool withSheetName, bool withWorkbookName) {
            if (range == null)
                throw new ArgumentNullException("range");
            string address;
            if (withWorkbookName) {
                address = range.Address[Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, true];
                int exIndex = address.LastIndexOf('!') + 1;
                address = address.Substring(0, exIndex) + address.Substring(exIndex).Replace("$", "");
            } else {
                address = range.Address[Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, false].Replace("$", "");
                if (withSheetName) {
                    var wsname = range.Worksheet.Name;
                    if (Regex.IsMatch(wsname, @"^\w+$"))
                        address = wsname + "!" + address;
                    else
                        address = "'" + wsname + "'!" + address;
                }
            }
            return address;
        }

        internal static string GetWorksheetAddress(Excel.Worksheet worksheet, bool withWorkbookName) {
            if (worksheet == null)
                throw new ArgumentNullException("worksheet");
            var wsAddress = new StringBuilder();
            if (withWorkbookName)
                wsAddress.Append("[" + ((Excel.Workbook)worksheet.Parent).Name + "]");
            wsAddress.Append(worksheet.Name);
            if (Regex.IsMatch(worksheet.Name, @"^\w+$"))
                return wsAddress + "!";
            return "'" + wsAddress + "'!";
        }

        internal static string GetWorkbookAddress(Excel.Workbook workbook) {
            if (workbook == null)
                throw new ArgumentNullException("workbook");
            return "[" + workbook.Name + "]";
        }

        internal static object DisposeWorkbook(ref Excel.Workbook workbook) {
            if (workbook != null) {
                workbook.Close(false, Type.Missing, Type.Missing);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
                workbook = null;
            }
            return null;
        }

        public static string GetColumnLetters(int columnNumber) {
            const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            string columnName = "";
            while (columnNumber > 0) {
                columnName = letters[(columnNumber - 1) % 26] + columnName;
                columnNumber = (columnNumber - 1) / 26;
            }
            return columnName;
        }

        public static object[] GetColumnsFormating(Excel.Range range) {
            int nbCol = range.Columns.Count;
            object[] formats = new object[nbCol];
            for (int i = 0; i < nbCol; i++) {
                var format = (string)((Excel.Range)range.Cells[2, i + 1]).NumberFormat;
                if (format != "General")
                    formats[i] = format;
            }
            return formats;
        }

        public static void SetColumnsFormat(Excel.Range range, int nbRows, object[] formats, int start, int increment) {
            for (int i = 0, ci = start; i < formats.Length; i++, ci += increment)
                ((Excel.Range)range.Columns[ci]).NumberFormat = formats[i];
        }

        internal static string GetCurrencyFormat(int lcid, string symbole, int position) {
            string format = "#,##0.00"; // +(nbDecimal == 0 ? "" : "." + new string('0', nbDecimal));
            string stag = "[$" + symbole + "-" + lcid.ToString("X").ToUpper() + "]";
            switch (position) {
                case 0: format = stag + format; break;
                case 1: format = format + stag; break;
                case 2: format = stag + " " + format; break;
                case 3: format = format + " " + stag; break;
            }
            return format;
        }

        internal static string JoinArray<T>(string separator, T[] values) {
            var sb = new StringBuilder();
            foreach (var element in values) {
                if (sb.Length != 0) sb.Append(separator);
                sb.Append(element.ToString());
            }
            return sb.ToString();
        }


    }
}
